/*============================================================================
  File:     CreateDatabaseSnapshotForAW.sql

  Summary:  Creates a database snapshot of the AdventureWorks sample database.
            This script uses advanced techniques to create a filegroup and file
            name list to use in the creation of the snapshot.  This enables
            the code to function correctly regardless of additional filegroups.

  Date:     August 24, 2004
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

SET NOCOUNT ON;

-- Change to the master database
USE [master];
GO

PRINT 'Drop existing snaphost';
PRINT '';

-- Drop the snapshot of the AdventureWorks database, if it exists
IF  EXISTS (SELECT [name] FROM [master].[dbo].[sysdatabases] WHERE [name] = N'AdventureWorksSnapshot')
DROP DATABASE [AdventureWorksSnapshot];
GO

PRINT 'Create the snaphost';
PRINT '';

-- Create a snapshot of the AdventureWorks database
DECLARE @DbName sysname;
DECLARE @Sql nvarchar(max);
DECLARE @Pos int;

SET @DbName = N'AdventureWorks';

EXECUTE (
N'DECLARE @Sql NVARCHAR(MAX); 

SET @Sql = N''CREATE DATABASE [' + @DbName + N'Snapshot] ON ''; 

SELECT @Sql = @Sql + CHAR(13) + N''(NAME=['' + [name] + N''],'' 
    + CHAR(13) + N''FILENAME=N'''''' +  
    LEFT([physical_name], 
        CHARINDEX(N''.'', 
            [physical_name], 
            LEN([physical_name]) - 5) - 1) 
    + N''Snapshot'' 
    + SUBSTRING([physical_name], 
        CHARINDEX(N''.'', 
            [physical_name], 
            LEN([physical_name]) - 5), 5) + N''''''),'' 
FROM [master].[sys].[master_files] 
WHERE [type] = 0 
    AND [database_id] = DB_ID(N''' + @DbName + N''');

SET @Sql = LEFT(@Sql, LEN(@Sql) - 1) + N'' AS SNAPSHOT OF [' + @DbName + N'];''

PRINT @Sql;
PRINT '''';

EXECUTE (@Sql);'
); 
GO

PRINT 'Display the snapshot databases'
PRINT '';

-- Display the snapshot databases
SELECT N'[' + db1.[name] + N'] is a snapshot of database ['+ db2.[name] + N']' AS 'Snapshot Databases'
FROM [master].[sys].[databases] db1 
    INNER JOIN [master].[sys].[databases] db2 
    ON db1.[source_database_id] = db2.[database_id];
GO
